package kael.aiur.excel;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelUtil {

	public List<Map<String, Object>> getExcelContentAsMap(String file) {
		List<String> columnName = new ArrayList<String>();
		List<Map<String, Object>> rowList = new ArrayList<Map<String, Object>>();
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(new File(file));
			Sheet sheet = wb.getSheetAt(0);
			Row firstRow = sheet.getRow(0);
			for (Cell cell : firstRow) {
				columnName.add(cell.getStringCellValue());
			}
			sheet.removeRow(firstRow);
			for (Row row : sheet) {
				Map<String, Object> rowContent = new HashMap<String, Object>();
				for (Cell cell : row) {
					Object val = null;
					cell.setCellType(Cell.CELL_TYPE_STRING);
					val = cell.getStringCellValue();
					rowContent.put(columnName.get(cell.getColumnIndex()), val);
				}
				rowList.add(rowContent);
			}
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {

		}
		return rowList;
	}

	public Map<String, Map<String, Integer>> repeat(String file) {
		List<Map<String, Object>> sheet = this.getExcelContentAsMap(file);
		if (sheet == null || sheet.size() == 0) {
			return null;
		}
		Map<String, Map<String, Integer>> map = new HashMap<String, Map<String, Integer>>();
		Set<String> keys = sheet.get(0).keySet();
		for (String key : keys) {
			Map<String, Integer> valueMap = new HashMap<String, Integer>();
			for (Map<String, Object> val : sheet) {
				String valStr = val.get(key) == null ? "" : val.get(key)
						.toString();
				if (valueMap.get(valStr) == null) {
					valueMap.put(valStr, 1);
				} else {
					valueMap.put(valStr, valueMap.get(valStr) + 1);
				}
			}
			map.put(key, valueMap);
		}
		return map;
	}
}
